/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import Enum.LoaiNguoiDungEnum;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import pojo.NguoiDung;
import util.NewHibernateUtil;

/**
 *
 * @author 0
 */
public class NguoiDungDAO extends BaseDAO {

    public static List<NguoiDung> TimKiemNhanVien(Integer maNhanVien, String tenNhanVien, String username, String cmnd, Integer idLoaiNhanVien, Integer idCuaHang, NguoiDung nguoiDung) throws Exception {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        String string = "from NguoiDung nd where (nd.isDelete=false or nd.isDelete=null) and nd.loaiNguoiDung.id!=1";
        if (nguoiDung.getLoaiNguoiDung().getId() != LoaiNguoiDungEnum.Admin.getCode()) {
            string += String.format(" and nd.cuaHang.id=%d", nguoiDung.getCuaHang().getId());
        }
        if (!tenNhanVien.isEmpty()) {
            string += " and lower(nd.tenNguoiDung) like :tenNhanVien";
        }
        if (!username.isEmpty()) {
            string += " and lower(nd.username) like :username";
        }
        if (!cmnd.isEmpty()) {
            string += " and lower(nd.cmnd) like :cmnd";
        }
        if (maNhanVien != null) {
            string += String.format(" and nd.id=%d", maNhanVien);
        }
        if (idLoaiNhanVien != null) {
            string += String.format(" and nd.loaiNguoiDung.id=%d", idLoaiNhanVien);
        }
        if (idCuaHang != null) {
            string += String.format(" and nd.cuaHang.id=%d", idCuaHang);
        }
        Query query = session.createQuery(string);
        if (!tenNhanVien.isEmpty()) {
            query.setString("tenNhanVien", "%" + tenNhanVien.toLowerCase() + "%");
        }
        if (!username.isEmpty()) {
            query.setString("username", "%" + username.toLowerCase() + "%");
        }
        if (!cmnd.isEmpty()) {
            query.setString("cmnd", "%" + cmnd.toLowerCase() + "%");
        }
        List<NguoiDung> list = query.list();
        session.close();
        return list;
    }

    public static int ThemNhanVien(NguoiDung nguoiDung) throws Exception {
        int id = 0;
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        Transaction transaction = session.beginTransaction();
        try {
            NguoiDung nd = (NguoiDung) session.createQuery(String.format("from NguoiDung nd where nd.cmnd = '%s'", nguoiDung.getCmnd()) ).uniqueResult();
            if (nd == null) {
                id = (int) session.save(nguoiDung);
                transaction.commit();
                session.close();
            } else {
                session.close();
                throw new Exception("CMND đã tồn tại");
            }
        } catch (HibernateException exception) {
            transaction.rollback();
            session.close();
            throw new Exception(exception.getMessage());
        }
        return id;
    }

    public static void SuaNhanVien(NguoiDung nguoiDung) throws Exception {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        Transaction transaction = session.beginTransaction();
        try {
            NguoiDung nd = (NguoiDung) session.createQuery(String.format("from NguoiDung nd where nd.cmnd = '%s' and nd.id!=%d", nguoiDung.getCmnd(), nguoiDung.getId())).uniqueResult();
            if (nd == null) {
                session.update(nguoiDung);
                transaction.commit();
                session.close();
            } else {
                session.close();
                throw new Exception("CMND đã tồn tại");
            }
        } catch (HibernateException exception) {
            transaction.rollback();
            session.close();
            throw new Exception(exception.getMessage());
        }
    }

    public static void SuaTaiKhoan(NguoiDung nguoiDung) throws Exception {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        Transaction transaction = session.beginTransaction();
        try {
            Query query = session.createQuery(String.format("from NguoiDung nd where nd.username = :username and nd.id!=%d", nguoiDung.getId()));
            NguoiDung nd = (NguoiDung) query.setString("username", nguoiDung.getUsername()).uniqueResult();
            if (nd == null) {
                session.update(nguoiDung);
                transaction.commit();
                session.close();
            } else {
                session.close();
                throw new Exception("Username đã tồn tại");
            }
        } catch (HibernateException exception) {
            transaction.rollback();
            session.close();
            throw new Exception(exception.getMessage());
        }
    }

    public static NguoiDung DangNhap(String username, String password) throws Exception {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        Query query = session.createQuery("from NguoiDung nd where nd.username=:username and password=:password");
        query.setString("username", username);
        query.setString("password", password);
        NguoiDung nguoiDung = (NguoiDung) query.uniqueResult();
        session.close();
        return nguoiDung;
    }
    
   /* public static List<NguoiDung> TimKiemKhachHang(Integer maKhachHang, String tenKhachHang, String cmnd) throws Exception {
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        String string = "from NguoiDung nd where (nd.isDelete=false or nd.isDelete=null)";
        
        if (!tenKhachHang.isEmpty() ) {
            string += " and lower(nd.tenNguoiDung) like :tenKhachHang";
        }
        
        if (!cmnd.isEmpty()) {
            string += " and lower(nd.cmnd) like :cmnd";
        }
        
        if (maKhachHang != null) {
            string += String.format(" and nd.id=%d", maKhachHang);
        }
        
        Query query = session.createQuery(string);
       if (!tenKhachHang.isEmpty()) {
            query.setString("tenKhachHang", "%" + tenKhachHang.toLowerCase() + "%");
        }
        if (!cmnd.isEmpty()) {
            query.setString("cmnd", "%" + cmnd.toLowerCase() + "%");
        }
        
        List<NguoiDung> list = query.list();
        session.close();
        return list;
    }*/
    
    public static List<NguoiDung> LayDanhSachNguoiDung()throws Exception{
        List<NguoiDung> ds = null;
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        String hql = "Select nd From NguoiDung nd where isDelete = FALSE";
        Query query = session.createQuery(hql);
        ds = query.list();
        session.close();
        return ds;
    }
    
    public static NguoiDung LayKhachHangByID(int ID){
        NguoiDung nd = null;
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        nd = (NguoiDung) session.get(NguoiDung.class, ID);
        session.close();      
        return nd;
    }
    
    public static boolean ThemKhachHang(NguoiDung nguoiDung){
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if(NguoiDungDAO.LayKhachHangByID(nguoiDung.getId()) != null){
            return false;
        }
        Transaction transaction = null;
        try {
            transaction = session.beginTransaction();
            session.save(nguoiDung);
            transaction.commit();
        } catch (Exception e) {
            transaction.rollback();
        }
        finally{
            session.close();
        }
        return true;
    }
    
    public static boolean CapNhatKhachHang(NguoiDung nguoiDung){
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        if(NguoiDungDAO.LayKhachHangByID(nguoiDung.getId()) == null){
            return false;
        }
        Transaction transaction = null;
        try {
            transaction = session.beginTransaction();
            session.update(nguoiDung);
            transaction.commit();
        } catch (Exception e) {
            transaction.rollback();
        }
        finally{
            session.close();
        }
        return true;
    }
    
    public static boolean XoaKhachHang(int ID){
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        NguoiDung nguoiDung = NguoiDungDAO.LayKhachHangByID(ID);
        if(nguoiDung == null){
            return false;
        }
        Transaction transaction = null;
        try {
            transaction = session.beginTransaction();
            session.delete(nguoiDung);
            transaction.commit();
        } catch (Exception e) {
            transaction.rollback();
        }
        finally{
            session.close();
        }
        return true;
    }
    
    public static List<NguoiDung> TimKhachHangTheoTen(String tenKhachHang){
        List<NguoiDung> ds = null;
        Session session = NewHibernateUtil.getSessionFactory().openSession();
        //String hql = "Select nd From NguoiDung nd where tenNguoiDung like '" + tenKhachHang + "'";
        String hql = "Select nd From NguoiDung nd where tenNguoiDung like '%" + tenKhachHang + "%'";
        Query query = session.createQuery(hql);

        ds = query.list();
        session.close();
        return ds;
    }
        
    public static List<NguoiDung> TimKiemKhachHang(int id, String tenKhachHang, String cmnd ){
        List<NguoiDung> ds = null;
         Session session = NewHibernateUtil.getSessionFactory().openSession();
         String hql = "Select nd From NguoiDung nd Where (isDelete = false or isDelete = null) ";
         
         //TH1: KHÔNG NHẬP CMND
         if(cmnd.isEmpty() && id != -1 && !(tenKhachHang.isEmpty()) ){
             hql = hql + " and id = " + id;
             hql = hql + " and tenNguoiDung like '%" + tenKhachHang + "%'";
         }
         //TH2: KHÔNG NHẬP TÊN KHÁCH HÀNG
         else if(tenKhachHang.isEmpty() && id != -1 && !(cmnd.isEmpty())  ){
             hql = hql + " and id = " + id;
             hql = hql + " and cmnd = '" + cmnd + "'";
         }
         //TH3: KHÔNG NHẬP CMND
         else if(id == -1 && !(tenKhachHang.isEmpty())  && !(cmnd.isEmpty()) ){
             hql = hql + " and tenNguoiDung like '%" + tenKhachHang + "%'";
             hql = hql + " and cmnd = '" + cmnd + "'";
         }
         
         //CHỈ NHẬP MA KHÁCH HÀNG
         else if(cmnd.isEmpty() && tenKhachHang.isEmpty()){
             hql = hql + " and id = " + id;
         }
         
         //CHỈ NHẬP TÊN KHÁCH HÀNG
         else if(cmnd.isEmpty() && id == -1){
             hql = hql + " and tenNguoiDung like '%" + tenKhachHang + "%'";
         }
         
         //CHỈ NHẬP CMND
         else if(tenKhachHang.isEmpty() && id == -1){
             hql = hql + " and cmnd = '" + cmnd + "'";
         }
         
         //NHẬP ĐỦ 3 THÔNG TIN
         else{
             hql = hql + " and id = " + id;
             hql = hql + " and tenNguoiDung like '%" + tenKhachHang + "%'";
             hql = hql + " and cmnd = '" + cmnd + "'";
         }
         
         Query query = session.createQuery(hql);
         ds = query.list();
         session.close();
        return ds;
    }
}
  
